from DataAccess.DBConnFactory import DBConnFactory
from Routine.UpdateFX import *
from Routine.UpdateStockPrice import *

from string import Template
import timeit


def update_security_to_latest_price(ref_date):
	#DEBUG
	start = timeit.default_timer()
	
	update_fx_daily_price(ref_date)
	update_CH_stock_daily_price(ref_date)
	update_HK_stock_daily_price(ref_date)
	update_US_stock_daily_price(ref_date)
	update_futures_daily_settlement_price(ref_date)	
	
	elapsed = (timeit.default_timer() - start)
	print 'update_security_to_latest_price, ', elapsed

	
def calc_portf_mkt_val(ref_date, after_clearing=False):	
	#DEBUG
	start = timeit.default_timer()
	
	#SHORT_CUT
	#update_security_to_latest_price(ref_date)
	conn = DBConnFactory().get_db_connection('PKEDB')
							
	sql_tpl = Template('''insert into portfolio_mkt_val 
				select TO_DATE('${DATE}','yyyy-mm-dd'),
					m2m.portfolio_id, 
					sum(round(m2m.mkt_val/fx.price,2)) mkt_val,
					pi.currency
				from m2m_fast m2m
				join portfolio_info pi 
					on pi.portfolio_id=m2m.portfolio_id 
				join comm_fx_latest fx 
					on fx.ref_date=m2m.ref_date
					and fx.dom_curncy=m2m.local_curncy 
					and fx.for_curncy=pi.currency
				where m2m.ref_date=TO_DATE('${DATE}','yyyy-mm-dd') 
				group by m2m.portfolio_id,pi.currency''')
										
	sql_tpl2 = Template('''update 
						(select /*+BYPASS_UJVC*/ mv.mkt_val_post,mv.mkt_val
						from portfolio_mkt_val mv
						join 
							(select cs.ref_date,
								pi.portfolio_id 
								sum(cs.amount/fx.price) cash_in
							from portfolio_info pi
							join cash_switch cs
							  on pi.portfolio_id=cs.portfolio_id
							join comm_fx_latest fx
							  on cs.ref_date=fx.ref_date
							  and fx.dom_curncy=cs.currency
							  and fx.for_curncy=pi.currency
							where cs.ref_date=TO_DATE('${DATE}','yyyy-mm-dd') 
							group by pi.portfolio_id
							)t
						on portfolio_mkt_val.ref_date=t.ref_date
							and portfolio_mkt_val.portfolio_id=t.portfolio_id)
						set mkt_val_post=mkt_val+cash_in''')
						
	if not after_clearing:				
		sql_text = sql_tpl.substitute(DATE=ref_date.isoformat())	
	else
		sql_text = sql_tpl2.substitute(DATE=ref_date.isoformat())	
	conn.cursor().execute(sql_text)
	conn.commit()
	
	elapsed = (timeit.default_timer() - start)
	print 'calc_portf_mkt_val, ', elapsed
